﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Collections.Generic;
using AjaxControlToolkit;
using System.Collections.Specialized;

namespace WebServiceEHotel
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService()]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService()]
    public class Service1 : System.Web.Services.WebService
    {

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

       private SqlConnection conn = new SqlConnection();
       private SqlCommand comm;
       private SqlTransaction tr;
     
       
       private string strcon = "Server=TRUEFASTER\\SQLEXPRESS;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\E-hotel.mdf;Database=E-hotel;Database=E-hotel; Trusted_Connection=Yes;";
       // Private strcon As String = "Data Source=" & ConfigurationManager.AppSettings("HOST") & ";User ID=" & ConfigurationManager.AppSettings("Username") & ";PWD=" & ConfigurationManager.AppSettings("Password") & ";initial catalog=" & ConfigurationManager.AppSettings("DatabaseName")
     
      
     
       [WebMethod()]
       public DataSet SelectData(string sql)
       {
           try
           {

               // execute 
               SqlDataAdapter da = default(SqlDataAdapter);
               DataSet ds = new DataSet();

               {
                   conn.ConnectionString = strcon;
                   conn.Open();
               }
               da = new SqlDataAdapter(sql, strcon);
               da.Fill(ds, "table");
               conn.Close();
               return ds;
           }
           catch (Exception ex)
           {
               throw new Exception("clsManageSql Error: {ExecuteNonQuery}-" + ex.Message);
           }
       }
      
      
      
      
       [WebMethod()]
       public bool Update(string sql)
       {
           try {
               comm = new SqlCommand();
               conn.ConnectionString = strcon;
               conn.Open();
               comm.Connection = conn;
               tr = conn.BeginTransaction();
               comm.Transaction = tr;
               comm.CommandType = CommandType.Text;
               comm.CommandText = sql;
               if (comm.ExecuteNonQuery() == 0) {
                   return false;
               }
               else {
                   return true;
               }
                  
             tr.Commit();
           }
          catch (Exception ex) {
               throw new Exception("clsManageSql Error: {Update}-" + ex.Message);
               tr.Rollback();
              return false;
           }
       }
      
       [WebMethod()]
      public bool Delete(string sql)
       {
           try {
               comm = new SqlCommand();
               conn.ConnectionString = strcon;
               conn.Open();
               comm.Connection = conn;
               tr = conn.BeginTransaction();
               comm.Transaction = tr;
               comm.CommandType = CommandType.Text;
               comm.CommandText = sql;
               comm.ExecuteNonQuery();
               tr.Commit();
               return true;
           }
           catch (Exception ex) {
               throw new Exception("clsManageSql Error: {Delete}-" + ex.Message);
               tr.Rollback();
              return false;
          }
      }
     
     
      [WebMethod()]
      public int getMaxID(string tableName, string fieldName)
      {
          try {
              string sql = "select max(" + fieldName + ")+ 1 from " + tableName;
             int i = 0;
              comm = new SqlCommand();
              conn.ConnectionString = strcon;
              conn.Open();
              comm.Connection = conn;
              tr = conn.BeginTransaction();
              comm.Transaction = tr;
              comm.CommandType = CommandType.Text;
              comm.CommandText = sql;
              try {
                  i = (int)comm.ExecuteScalar();
                  return i;
              }
              catch (Exception ex) {
                  return 1;
              }
                 
              tr.Commit();
          }
          catch (Exception ex) {
              throw new Exception("clsManageSql Error: {getMaxID}-" + ex.Message);
              tr.Rollback();
              return 0;
          }
      }
     
      [WebMethod()]
      public bool Insert(string sql)
      {
          try {
              comm = new SqlCommand();
              conn.ConnectionString = strcon;
              conn.Open();
              comm.Connection = conn;
              tr = conn.BeginTransaction();
              comm.Transaction = tr;
              comm.CommandType = CommandType.Text;
              comm.CommandText = sql;
              comm.ExecuteNonQuery();
              tr.Commit();
              return true;
          }
          catch (Exception ex) {           
            throw new Exception("clsManageSql Error: {Insert}-" + ex.Message);
              tr.Rollback();
             return false;
         }
      }
     
    
      [WebMethod()]
     public bool ExecuteDataHaving(string sql)
     {
          try {
             
              // execute
              SqlDataAdapter da = default(SqlDataAdapter);
              DataSet ds = new DataSet();
            
              {
                  conn.ConnectionString = strcon;
                  conn.Open();
              }
            
              da = new SqlDataAdapter(sql, strcon);
              da.Fill(ds, "table");
              conn.Close();
              if (ds.Tables[0].Rows.Count > 0) {
                 return true;
              }
              else {
                  return false;
                 
              }
          }
          catch (Exception ex) {
              throw new Exception("clsManageSql Error: {ExecuteDataHaving}-" + ex.Message);
          }
          finally {
          }
      }




    }
}
